import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql


if __name__ == '__main__':
    engine = create_engine("mysql+pymysql://root:123456@localhost:3306/transportationdata?charset=utf8")
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='transportationdata', charset='utf8')
    # 车牌过滤
    plateData = pd.read_sql("select * from plate_table where plate_num!='-'", con=conn)
    # 路口过滤
    junctionRelationData = pd.read_sql("select monitor_id from junction_relation_table", con=conn)
    plateData = plateData.merge(junctionRelationData, on="monitor_id", how="left")
    plateData = plateData.dropna()

    junctionRelationData = pd.read_sql("select * from junction_relation_table", con=conn)
    # 初始化无穷大值
    junctionRelationData["free_flow_time"] = np.inf

    # 根据车牌分组
    for name, group in plateData.groupby("plate_num"):
        # 过滤数量不足的数据
        if len(group) <= 1:
            continue
        # 根据时间逆序
        group = group.sort_values(by="pass_time", ascending=False)
        # 顺序访问前后两行数据
        for i in range(1, len(group)):
            curData = group.iloc[i - 1]
            preData = group.iloc[i]

            cur = curData["monitor_id"]
            direction = curData["Direction"]
            pre = preData["monitor_id"]
            # 前后路口一致，数据无意义
            if cur == pre:
                continue
            # 间隔时间计算
            interval = pd.to_datetime(curData["pass_time"]) - pd.to_datetime(preData["pass_time"])
            interval = interval.total_seconds()
            # 时间间隔小于30s，数据无意义
            if interval < 30:
                continue
            # 指定上下游路口筛选
            res = junctionRelationData.loc[(junctionRelationData["monitor_id"] == cur) & (junctionRelationData["direction"] == direction) & (junctionRelationData["pre"] == pre)]
            if len(res) == 1:
                res = res.iloc[0]
                # 自由流时间等于间隔时间的最小值
                junctionRelationData.loc[(junctionRelationData["monitor_id"] == cur) & (junctionRelationData["direction"] == direction) & (junctionRelationData["pre"] == pre), "free_flow_time"] = min(res["free_flow_time"], interval)
                print(interval)
    junctionRelationData = junctionRelationData.replace(np.inf, np.nan)
    junctionRelationData.to_sql("free_flow_time_table", con=engine, if_exists="replace", index=False)
